﻿CREATE PROCEDURE [dbo].[GetObjectData]
	@unique_num int
AS
	
begin

declare @class_num int  
declare @new_class_num int  
select @class_num = Class from UObject where UniqueNo = @unique_num;

declare @table_name varchar(255)


declare @classes table(indexer int, class_num int, table_name varchar(255));
declare @indexer int;
set @indexer = 1;

while (@class_num  > 0)
begin
	select @new_class_num = parent_num , @table_name = table_name from ClassesNames where class_num = @class_num;

	insert into @classes values (@indexer, @class_num, @table_name);
	set @class_num = @new_class_num;
	set @indexer += 1;
end

DECLARE classes_cursor CURSOR FOR 
SELECT class_num, table_name 
FROM @classes 
ORDER BY indexer desc;

OPEN classes_cursor 

FETCH NEXT FROM classes_cursor 
INTO @class_num, @table_name;

declare @fields table(FieldName varchar(255),FieldType int);
declare @result table(class_num int, FieldName varchar(255), FieldType int, FieldData varchar(255));

WHILE @@FETCH_STATUS = 0
BEGIN
    
	insert into @fields
		select FieldName, FieldType from ClassesFields where class_num = @class_num;


    DECLARE fields_cursor CURSOR FOR 
    SELECT FieldName, FieldType 
    FROM @fields;
	
    declare @FieldName varchar(255);
    declare @FieldType varchar(255);
    declare @fieldData varchar(255);
    declare @script varchar(255);
    OPEN fields_cursor 
    FETCH NEXT FROM fields_cursor 
	INTO @FieldName,@FieldType;

    WHILE @@FETCH_STATUS = 0
    BEGIN
print @FieldName + ' '+@table_name
		set @script = 'select convert(varchar(255),'+@FieldName+') as result from dbo.'+@table_name+' where UniqueNo = ' + convert(varchar(255),@unique_num)
		--exec @fieldData = ExecuteScript @script, @FieldName, @table_name, @unique_num;
		declare @SPresult table (result varchar(255))
		insert into @SPresult
		exec(@script)
		select @fieldData = result from @SPresult
		delete from @SPresult
		--select @fieldData = convert(varchar(255),@FieldName) from @table_name where UniqueNo = @unique_num

		insert into @result values(@class_num,@FieldName,@FieldType,@fieldData);

		FETCH NEXT FROM fields_cursor 
		INTO @FieldName,@FieldType;
    END;
	
    CLOSE fields_cursor;
    DEALLOCATE fields_cursor
	
	FETCH NEXT FROM classes_cursor 
	INTO @class_num, @table_name;
	
	delete from @fields
END

CLOSE classes_cursor;
DEALLOCATE classes_cursor;

select * from @result

end